package com.zjf.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.zjf.dao.UserDao;
import com.zjf.db.DBManager;
import com.zjf.entity.User;

public class UserDaoImpl implements UserDao {
	private Connection conn;
	private String defaultHeadUrl ="upload/head/default.jpg";
	public void addUser(User user) throws Exception {
		PreparedStatement stmt = null;
		try{conn = DBManager.getConnection();
		List<User> users = new ArrayList<User>();
		String sql = "insert into b_user values(null,?,?,?,?,?,?,now(),?,?,?)";
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, user.getUsername());
		stmt.setString(2, user.getPassword());
		stmt.setString(3, user.getName());
		stmt.setInt(4, 0);
		stmt.setInt(5,user.getSex());
		stmt.setString(6,user.getEmail());
		stmt.setInt(7, 0);
		stmt.setInt(8, 0);
		stmt.setString(9, defaultHeadUrl);
		stmt.executeUpdate();
		}finally{
		DBManager.close(conn, stmt);
		}
	}

	public void delete(long id) throws Exception {
		Statement stmt =  null;
		try{conn = DBManager.getConnection();
		stmt = conn.createStatement();
		stmt.execute("delete from b_user where id = "+id);
		
		}finally{
			DBManager.close(conn, stmt);
		}
	}

	public User queryUser(long id) throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		try{conn = DBManager.getConnection();
		stmt = conn.createStatement();
		String sql ="select * from b_user where id = "+id;
		rs = stmt.executeQuery(sql);
		User user = new User();
		rs.next();
		user.setId(rs.getLong("id"));
		user.setUsername(rs.getString("u_username"));
		user.setPassword(rs.getString("u_password"));
		user.setName(rs.getString("u_name"));
		user.setPermisson(rs.getInt("u_permission"));
		user.setSex(rs.getInt("u_sex"));
		user.setEmail(rs.getString("u_email"));
		user.setRegDate(rs.getString("u_regDate"));
		user.setArticleNum(rs.getInt("u_articlecount"));
		user.setReviewNum(rs.getInt("u_reviewcount"));
		user.setHeadUrl(rs.getString("u_headurl"));
		
		return user;
		}finally{
			DBManager.close(conn, stmt,rs);
		}
	}

	public User queryUser(String username) throws Exception {
		Statement stmt = null;
		ResultSet rs =  null;
		try{conn = DBManager.getConnection();
		stmt = conn.createStatement();
		String sql ="select * from b_user where u_username = '"+username+"'";
		rs = stmt.executeQuery(sql);
		User user = new User();
		if(rs.next()){
			user.setId(rs.getLong("id"));
			user.setUsername(rs.getString("u_username"));
			user.setPassword(rs.getString("u_password"));
			user.setName(rs.getString("u_name"));
			user.setPermisson(rs.getInt("u_permission"));
			user.setSex(rs.getInt("u_sex"));
			user.setRegDate(rs.getString("u_regDate"));
			user.setArticleNum(rs.getInt("u_articlecount"));
			user.setReviewNum(rs.getInt("u_reviewcount"));
			user.setHeadUrl(rs.getString("u_headurl"));
			
			return user;
		}else{
			return null;
		}
		}finally{
			DBManager.close(conn, stmt,rs);
		}
	}

	public ArrayList<User> queryAllUsers() throws Exception {
		Statement stmt =  null;
		ResultSet rs = null;
		try{conn = DBManager.getConnection();
		stmt = conn.createStatement();
		String sql ="select * from b_user order by u_regDate desc";
		rs = stmt.executeQuery(sql);
		List<User> users = new ArrayList<User>();
		while(rs.next()){
			User user = new User();
			user.setId(rs.getLong("id"));
			user.setUsername(rs.getString("u_username"));
			user.setPassword(rs.getString("u_password"));
			user.setName(rs.getString("u_name"));
			user.setPermisson(rs.getInt("u_permission"));
			user.setSex(rs.getInt("u_sex"));
			user.setRegDate(rs.getString("u_regDate"));
			user.setArticleNum(rs.getInt("u_articlecount"));
			user.setReviewNum(rs.getInt("u_reviewcount"));
			user.setHeadUrl(rs.getString("u_headurl"));
			users.add(user);
		}
		
		return (ArrayList<User>) users;
		}finally{
			DBManager.close(conn, stmt,rs);
		}
	}
	
	public void modify(long id,User u) throws Exception {
		PreparedStatement stmt  = null;
		
		try{conn = DBManager.getConnection();
		String sql  ="";
		if(u.getPassword()==null){
			sql = "update b_user set u_name=?,u_email=?,u_permission=? where id = "+id;
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, u.getName());
			stmt.setString(2, u.getEmail());
			stmt.setInt(3, u.getPermisson());
			stmt.executeUpdate();
		
		}else{
			sql = "update b_user set u_password=?,u_name=?,u_email=?,u_permission=? where id = "+id;
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, u.getPassword());
			stmt.setString(2, u.getName());
			stmt.setString(3, u.getEmail());
			stmt.setInt(4, u.getPermisson());
			stmt.executeUpdate();
		}
		}finally{
			DBManager.close(conn, stmt);
		}
	}

	public void modifyHeadUrl(long id, String headUrl) throws Exception {
		PreparedStatement stmt = null;
		try{conn = DBManager.getConnection();
		String sql ="update b_user set u_headUrl = ? where id = "+id;
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, headUrl);
		stmt.executeUpdate();
		
		}finally{
			DBManager.close(conn, stmt);
		}
	}

}
